//
//  SQLiteDataBaseManager.m
//  18-SQLite
//
//  Created by Snow on 16/8/8.
//  Copyright © 2016年 Snow. All rights reserved.
//

#import "SQLiteDataBaseManager.h"
#import "Student.h"
@implementation SQLiteDataBaseManager
//单例的创建方式, 只有一个对象
//内存管理中, 单例不能被释放
+ (SQLiteDataBaseManager *)sharedManager
{
    static SQLiteDataBaseManager *manager = nil;
    //保证线程安全 只执行一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[SQLiteDataBaseManager alloc] init];
    });
    return manager;
}
- (BOOL)openSQLite3
{
    NSArray *pathArray = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *document = [pathArray firstObject];
    //数据库SQLite3文件类型 .db
    NSString *dbPath = [document stringByAppendingPathComponent:@"student.db"];
    //utf8属性 将oc字符串转换成c语言字符串
    //参数1: 数据库文件路径
    //参数2: 指向指针的指针 该指针可以被修改 数据库管理指针
    NSLog(@"%@", dbPath);
   int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    //sqlite3_open 当文件路径不存在时 文件会被SQLite创建并打开
    //当路径文件存在时 会被直接打开
    //sqlite3_open 返回值为int型, 0 SQLite_OK  代表成功 , 1 代表失败 .
    //SQLite_OK
    return [self isOperationResult:result alertText:@"打开数据库"];
}
- (BOOL)closeSQLite3
{
    //关闭数据时 如果数据库处于打开状态 则正常关闭 显示成功
    //如果数据库处于关闭状态 则显示失败 原因21 库不存在
    int result = sqlite3_close(dbPoint);
    return [self isOperationResult:result alertText:@"关闭数据库"];
}
- (BOOL)isOperationResult:(int)result alertText:(NSString *)text
{
    BOOL isOperation = YES;
    if (result == SQLITE_OK) {
        NSLog(@"%@成功", text);
        isOperation = YES;
    } else {
        NSLog(@"%@失败---%d", text, result);
        isOperation = NO;
    }
    return isOperation;
}
- (BOOL)createTable
{
    //SQL语句
    //创建表
    //create table 表名 (字段1 类型 约束, 字段2, 字段3...)
    
    //常用的约束
    //primary key 一个表 有且只能有一个主键
    //autoincrement 添加完该字段 系统会自己创建一个数据库sqlite_sequence 用来保存自增的值
    //not null 不能为空 为空会报SQL错误
    //删除表
    //drop table 表名
    NSString *createSQL = @"create table stu2 (stu_id integer primary key autoincrement, stu_name text not null)";
   int result = sqlite3_exec(dbPoint, [createSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"创建表"];
}
- (BOOL)insertStu:(Student *)stu
{
//    NSString *insertSQL = [NSString stringWithFormat:@"insert into stu1 (%ld, '%@')", stu.stu_id, stu.stu_name];
    //自增字段 autoincrement
    //默认如果填入null 值会从1开始
    //如果填入其他值 会从其他值开始 例:填入110 值会从110开始计算 并进行存储
    //存储在sqlite_sequence中
    NSString *insertSQL = @"insert into stu1 values (110, 'ggg')";
   int result = sqlite3_exec(dbPoint, [insertSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"添加数据"];
}
- (BOOL)deleteStu:(Student *)stu
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from stu1 where stu_id=%ld", stu.stu_id];
   int result = sqlite3_exec(dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"删除一条数据"];
}
- (BOOL)deleteAll
{
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from stu1 where 1=1"];
    int result = sqlite3_exec(dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"删除全部数据"];
}
- (BOOL)updateOldName:(NSString *)oldName
              newName:(NSString *)newName
{
    //update 表名 set 字段名 = 新值 where 字段名 = 旧值
    NSString *updateSQL = [NSString stringWithFormat:@"update stu1 set stu_name = '%@' where stu_name = '%@'", newName , oldName];
    int result = sqlite3_exec(dbPoint, [updateSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"更新数据"];
}
- (__kindof NSArray *)searchAll
{
    NSMutableArray *stuArray = [NSMutableArray array];
    sqlite3_stmt *stmt = NULL;
    //select 字段名, 字段名...from 表名 where 条件
    //参数1:数据库指针 SQLite3 *
    //参数2:SQLUI局的长度
    //参数3:SQL语句的长度 -1
    //参数4: 替身
    NSString *selectAll = @"select * from stu1";
   int result = sqlite3_prepare(dbPoint, [selectAll UTF8String], -1, &stmt, NULL);
    //如果查询结果为成功,
    if (result == SQLITE_OK) {
        //循环, 遍历替身内容,
        //step 查询替身中有没有其他数据
        //当包含其他数据时, 结果为SQLITE_ROW
        //当不包含其他数据时, 结果为SQLITE_DONE
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //循环体中 取对应的每一列数据
            //取出第0列的数据 根据类型 选择函数 int
           int stu_id = sqlite3_column_int(stmt, 0);
            //取出第一列的数据 根据类型选择函数 text
            const unsigned char *stu_name = sqlite3_column_text(stmt, 1);
            Student *stu = [[Student alloc] init];
            stu.stu_id = stu_id;
            stu.stu_name = [NSString stringWithUTF8String:(const char *)stu_name];
            [stuArray addObject:stu];
        }
    }
    //x循环结束处理完数据后 销毁替身
    sqlite3_finalize(stmt);
    return stuArray;
    
}
@end
